﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class DataPage : System.Web.UI.Page
{
    public string studentid; //用于保存表格修改之前的id，以便表格修改之后在数据库中找到相应行
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定


    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {

            DataSet m_ds = new DataSet();
            SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
            SqlCommand cmd = new SqlCommand("SELECT * FROM STU_INFO", myConn);
            myConn.Open();

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(m_ds, "student");     
            ta = m_ds.Tables["student"];   //保存表格到ta中
            dg1.DataSource = m_ds.Tables["student"].DefaultView;
            dg1.DataBind();
            myConn.Close();
            ViewState.Add("ds", m_ds);  //viesstate保存数据集m_ds

        }

    }

    public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs e)//datagrid中点击编辑edit时执行函数
    {

        vi = new DataView(ta);                                          //从ta中获取数据源用于绑定

        dg1.EditItemIndex = e.Item.ItemIndex;
        dg1.DataSource = vi;
        dg1.DataBind();

    }

    public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，点击cancel执行函数
    {

        dg1.EditItemIndex = -1;
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();

    }
   
    public void MyDataGrid_Update(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，修改表格数据后，点击update执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize+e.Item.ItemIndex;    //获取修改行的索引号(需要加上分页计算)
        //string indexx = e.Item.ItemIndex;

        TextBox IDText = (TextBox)e.Item.Cells[1].Controls[0];               //此三行获取表格中修改行的所有数据，以便下面程序删除该行，重新增加新行
        TextBox NAMEText = (TextBox)e.Item.Cells[2].Controls[0];
        TextBox GENDERText = (TextBox)e.Item.Cells[3].Controls[0];
        TextBox COLLEGEIDText = (TextBox)e.Item.Cells[4].Controls[0];
        TextBox SPECIALITYText = (TextBox)e.Item.Cells[5].Controls[0];
        TextBox CLASSText = (TextBox)e.Item.Cells[6].Controls[0];
        TextBox PHONEText = (TextBox)e.Item.Cells[7].Controls[0];
        TextBox EMAILText = (TextBox)e.Item.Cells[8].Controls[0];


        String ID = IDText.Text;
        String NAME = NAMEText.Text;
        String GENDER = GENDERText.Text;
        String COLLEGEID = COLLEGEIDText.Text;
        String SPECIALITY = SPECIALITYText.Text;
        String CLASS = CLASSText.Text;
        String PHONE = PHONEText.Text;
        String EMAIL = EMAILText.Text;


        DataRow dr = ta.NewRow();                  //创建一个ta类型中的新行

        dr[0] = ID; dr[1] = NAME; dr[2] = GENDER; dr[3] = COLLEGEID; dr[4] = SPECIALITY; dr[5] = CLASS; dr[6] = PHONE; dr[7] = EMAIL;
        studentid = (string)ta.Rows[index][0];                              //因为id主键也可能会被修改，所以提前保存未被修改的id

        ta.Rows.RemoveAt(index);              //删除ta中的旧行，添加新行
        ta.Rows.InsertAt(dr, index);

        vi = new DataView(ta);               //重新数据绑定vi
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();


        //对数据库操作，更新数据，此处会用到上面保存的studentid
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("update STU_INFO set id='" + dr[0].ToString() + "',name='" + dr[1].ToString() + "',gender='" + dr[2].ToString() + "',collegeid='" + dr[3].ToString() + "',speciality='" + dr[4].ToString() + "',class='" + dr[5].ToString() + "',phone='" + dr[6].ToString() + "',email='" + dr[7].ToString() + "' where id='" + studentid + "';", myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();




    }

    public void MyDataGrid_Delete(Object sender, DataGridCommandEventArgs e)  //datagrid中删除行时执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;             //获取被删除行的索引
        studentid = (string)ta.Rows[index][0];    //保存主键id
        ta.Rows.RemoveAt(index);                  //删除旧行

        vi = new DataView(ta);                    //重新绑定
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();

        //更新数据库数据
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("delete from STU_INFO where id='" + studentid + "';", myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();

    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }

    protected void Button1_Click(object sender, EventArgs e) //点击按钮，获取当前显示的datagrid表格中的数据，保存成excel格式
    {
        ToExcel(dg1);
    }

    public void ToExcel(System.Web.UI.Control ctl)   //将当前显示表格数据保存为excel
    {

        DataTable dt = new DataTable();
        dt = ta;                           //取得ta中数据，输出到excel
        StringWriter sw = new StringWriter();
        sw.WriteLine("id\t" + "name\t" + "gender\t" + "collegeid\t" + "speciality\t" + "class\t" + "phone\t" + "email\t"); //在excel中需要显示的列名，注意要根据具体情况自己处理*****
        foreach (DataRow dr in dt.Rows)
        {
            sw.WriteLine(dr["id"] + "\t" + dr["name"] + "\t" + dr["gender"] + "\t" + dr["collegeid"] + "\t" + dr["speciality"] + "\t" + dr["class"] + "\t" + dr["phone"] + "\t" + dr["email"]);
        }
        sw.Close();
        Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Write(sw);
        Response.End();

    }

    public void Alert(string str)//用于测试，没有具体用途
    {
        HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
    }
    
}
